The NorthwindCE
sample application illustrates how to develop a mobile application capable of
offline storage using Microsoft® SQL Server™ 2000 Windows® CE Edition
(SQL Server CE) and the .NET Compact Framework. The application highlights the design of forms and controls, data
access against a local SQL Server CE database, and synchronization to a SQL
Server database through replication. NorthwindCE is
made up of four controls embedded into a single form, FormNorthwindCE.cs.
Each NorthwindCE control contains complete and
thoroughly documented source code that can be reused as templates when
designing new applications.
The four controls in the application are:
ControlCustomer,
ControlEmployees,
ControlOrders,
and ControlReplication,
which are referred to, respectively, as the Customer control, Employees
control, Orders control, and Replication control. The Customer control, Employees control, and Orders
control read data from and write data to the Customers, Employees,
and Orders tables in a copy of the Northwind database on the device.Customer
and order data can be accessed and employee information updated using these
controls. The Replication control
accepts connection settings and uses this information to connect to a remote
SQL Server database. The local database is then synchronized to the server
database through replication.
NorthwindCE is designed for Pocket PC devices and the Pocket PC emulator. Modify
the user interface of the sample application for use with other devices.
Requirements
To run NorthwindCE,
you must already have the following software installed on your computer:
·
SQL Server 2000 Service Pack 1 or
higher
·
Internet Information Services (IIS)
For more information, see
"Installing Internet Information Services and Internet Information
Server" in Visual Studio .NET Software
Requirements.
·
SQL Server CE (included with Visual
Studio .NET)
·
SQL Server CE 2.0 Server Tools
The SQL Server CE
2.0 Server Tools installers, sqlce20sql2ksp1.exe for SQL Server Service Pack 1 and
sqlce20sql2ksp2.exe for SQL Server Service Pack 2, come with Visual Studio
.NET. These files are located by default in C:\Program
Files\Microsoft Visual Studio .NET\CompactFrameworkSDK\v1.0.5000\Windows CE\.
The SQL Server CE Connectivity Management utility and the SQL Server CE Virtual
Directory Creation Wizard are automatically installed by running one of the SQL
Server CE Server Tools installers on the computer running IIS.
At the end of the
setup, you can optionally launch the SQL Server CE Virtual Directory Creation
Wizard to configure the computer running IIS. Alternatively, you can go to the
Start menu, point to Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS. Launch
the wizard by double-clicking Create a
Virtual Directory in the right pane of the SQL Server CE Management
utility. To configure the computer
running IIS, follow the instructions provided by the wizard.
Configuring SQL Server
After Microsoft® Internet Information
Services (IIS) is properly configured, you can enable
Microsoft SQL Server 2000 for replication. The replication sample application uses
a duplicated Northwind database called Nwind_SQLCE.
Running the setup script SetupRepl.bat on the
computer running SQL Server 2000 creates the Nwind_SQLCE database, enables
SQL Server for replication, and creates a publication named SQLCEReplDemoNet.
To configure the computer running SQL
Server 2000, perform the following steps. If the computer running SQL Server is
already configured as a Publisher/Distributor for replication and has a
snapshot folder defined with the appropriate permissions, skip Step 1 and Step
2.
1.
Create a shared directory on the
computer running SQL Server 2000 that will become the Publisher/Distributor.
Replication snapshot files are created in this directory by the replication
process and are read by the SQL Server CE Subscriber.
2.
Ensure that the IIS user specified
during synchronization has read access to the shared directory. By default, the
virtual directory is configured with anonymous access; therefore, grant computername\IUSR_computername read access to the shared directory
created in Step 1.
3.
Run SetupRepl.bat,
located with the NorthwindCE sample:
Usage: SetupRepl {required parms}
{optional parms}
Required
Arguments:
{snapshot
share} - a share available for the storage of snapshot
files. All Subscribers (IIS user) must be able to read from this share.
{publisher login} - Publisher user ID.
{publisher password} - Publisher password.
Optional
Arguments:
{publisher} - Publisher name. Defaults to
local computer.
{snapshot
server name} - Defaults to Publisher name (Must be set to support named
instances).
Examples:
SetupRepl ReplSnapshot
sa password
SetupRepl ReplSnapshot
sa password Publisher
SetupRepl ReplSnapshot
sa password
Publisher\instance ServerName
This step installs a Publisher with a
local Distributor, creates a database named Nwind_SQLCE, and populates it
with initial data that matches data in the Northwind
database that ships with SQL Server. It also creates a merge publication named SQLCEReplDemoNet from the Nwind_SQLCE database.
The Distributor is installed using
the shared directory specified when the Setuprepl.bat
file is run. This shared directory is used as the replication snapshot folder.
For example, if you call Setuprepl.bat and specify ReplSnapshot as the snapshot shared directory, the
directory for all snapshot files is \\computername\ReplSnapshot.
4.
Grant the IIS user (NT_Authentication) or the SQL Server user (DB_Authentication) access to the publication access list (PAL).
Building and Launching the Sample
This sample
application is available in Visual Basic and Visual C#. After completing the
following procedures you can run the application.
To build and
launch the sample
1.
Copy the NorthwindCE
sample files into a solution directory on your development computer.
The Visual Basic files are located in
Program Files\Microsoft Visual Studio .NET\CompactFrameworkSDK\v1.0.5000\Samples\PocketPC\VB\NorthwindCE.
The Visual C# files are located in
Program Files\Microsoft Visual
Studio .NET\CompactFrameworkSDK\v1.0.5000\Samples\PocketPC\VC#\NorthwindCE.
2.
On the File menu, click Open
Solution, and load NorthwindCE.sln.
3.
In the Deployment Device box
on the Device toolbar, select the device you want to target.
4.
On the Debug menu, click Start
Without Debugging.
The application is compiled, deployed
to the target device (along with any additional libraries required for run
time), and launched on the device. On the device, the executable is located at \Windows\Start
Menu\NorthwindDemo.exe.
Using the Application
There is a standard menu at the bottom
of the screen for all four controls (Replication,
Customers, Employees, and Orders).
The menu has the following options:
|
Menu |
Actions |
|
File |
Exit : Closes the NorthwindCE application. |
|
Northwind |
Employees : Loads the Employees control. Customers : Loads the Customers control. |
|
Sync |
Replication : Loads the Replication control. |
Replication Control
The Replication control allows you to synchronize to a SQL Server
database and replicate information to a local database. Navigate to this
control using the Replication command on the Sync menu. The Replication control is always displayed
first; it
checks whether there is a local database containing the necessary data for the
other controls. Because the Customers, Employees, and Orders
controls access data from the local database, the local database must be
present for those controls to function. If a local database does not exist, the
Northwind menu option is disabled to prevent access
to other controls. If a local database exists, the fields in this control are
populated with default values from the database.
The following properties are required
for replication with a SQL Server database.
|
Property |
Value |
Comment |
|
Authentication |
<SQL> or
<Windows> |
SQL
authentication requires a user ID and password to be specified. Windows
Authentication does not require a user ID and password. . |
|
Publisher |
<Database Server
Name> |
The name of the
computer running SQL Server. |
|
User ID |
<default sa> |
The user ID is
required only if SQL authentication is used. |
|
Password |
<default
blank> |
A password is
required only if SQL Server authentication is used. |
|
Internet URL |
http://<IIS
Server Name>/ <virtual directory>/sscesa20.dll |
This URL points
to the location of the SQL Server CE 2.0 Server Agent file (sscesa20.dll).
The IIS server name is the name of the computer running IIS. The virtual
directory is the same directory name used when configuring IIS through the SQL
Server CE Virtual Directory Creation Wizard. |
|
Internet Login |
<default
blank> |
The Internet
login is required only if security is set on IIS to require a login and
password. |
|
Internet
Password |
<default
blank> |
The Internet
password is required only if security is set on IIS to require a login and
password. |
|
Subscriber |
<SQLCESub#1>
|
The Subscriber
field is used to identify the Subscriber. |
There are two buttons on this
control: Initialize and Reset.
Initialize begins a replication session to the specified Publisher using
the specified connection settings. A local database, NorthwindDemo.sdf
is created. The connection fields are then disabled after replication has
finished. Reset deletes the local NorthwindDemo database and
enables the connection setting fields so they can be changed.
A sample copy of the NorthwindDemo.sdf SQL Server CE database is included with
the NorthwindCE Visual Studio.NET project. When NorthwindDemo.sdf is copied onto the device, you do not
have to complete the steps above to configure replication before you can begin
using the NorthwindCE application. All features of
the NorthwindCE application will be available except
for synchronization using replication. To use replication, you must click Reset and then complete all
configuration steps listed above.
To copy NorthwindDemo.sdf
onto the device, open the NorthwindCE.sln Visual
Studio.NET solution. Right click on the NorthwindDemo.sdf
file in the Solutions Explorer and select Properties. In the Properties window,
click on Build Action and select Content from the drop down box. Open the file FormNorthwindCE.vb or FormNorthwindCE.cs
depending on which version you have and follow the instructions in comments
within the constructor of the class before building the application.
Employees Control
The Employees control allows you to select employee data from the Employees table in the local NorthwindDemo database
(NorthwindDemo.sdf). Selecting an employee name from
the combo box retrieves the corresponding Title,
Address, City, Region, PostalCode, HomePhone,
HireDate,
and EmployeeID
column values from the Employees
table. All columns are editable except HireDate and EmployeeID. Clicking the Save button persists the
changes to the local SQL Server CE database.
Customer Control
The Customer control allows you to select customer data from the Customers table in the local NorthwindDemo
database. Selecting a customer name from the combo box retrieves the
corresponding Address, City, PostalCode, Country, Phone, and ContactTitle
column values from the Customers
table. None of the fields on the Customer
control are editable; the Customers
table is read-only.
Orders Control
The Orders control allows you to select orders and order details from the Orders and OrderDetails tables in the local NorthwindDemo database. Selecting a customer name from the combo box retrieves the order records for that customer; the OrderID, OrderDate, RequiredDate, ShippedDate, and ShipVia column values are displayed. Selecting a specific order brings up the corresponding records for that order from the OrderDetails table; the ProductID, UnitPrice, Quantity, and Discount column values are displayed. None of fields on the Orders control are editable; both the Orders and OrderDetails tables are read-only.